﻿using System;
using System.Data;
using System.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace CommonUtils
{
    public class NpoiUtil
    {
        public static void TestCreate()
        {
            var excel = new XSSFWorkbook();
            var sheet = excel.CreateSheet();

            var row0 = sheet.CreateRow(0);
            row0.CreateCell(0).SetCellValue("姓名");
            row0.CreateCell(1).SetCellValue("性别");
            row0.CreateCell(2).SetCellValue("爱好");
            row0.CreateCell(3).SetCellValue("籍贯");
            row0.CreateCell(4).SetCellValue("备注");

            var row1 = sheet.CreateRow(1);
            row1.CreateCell(0).SetCellValue("霸道");
            row1.CreateCell(1).SetCellValue("男");
            row1.CreateCell(2).SetCellValue("编程");
            row1.CreateCell(3).SetCellValue("山东");
            row1.CreateCell(4).SetCellValue("好人");

            var path = "D:/" + DateTime.Now.ToString("yyyy-MM-dd hhmmss.fff") + ".xlsx";
            var stream = FileUtil.GetCreateStream(path);
            excel.Write(stream);
            stream.Close();
            LogUtil.PrintInfo(path);
        }

        public static DataTable GetDataTable(string path)
        {
            var extension = FileUtil.GetExtension(path);
            var stream = FileUtil.GetReadStream(path);
            ISheet sheet = null;
            if (extension == ".xlsx")
                sheet = new XSSFWorkbook(stream).GetSheetAt(0);
            else if (extension == ".xls")
                sheet = new HSSFWorkbook(stream).GetSheetAt(0);
            stream.Close();
            if (null == sheet)
                throw new Exception("不支持的文件类型");

            var dataTable = new DataTable();
            var titleSize = 0;
            for (var rowNum = sheet.FirstRowNum; rowNum <= sheet.LastRowNum; rowNum++)
            {
                var row = sheet.GetRow(rowNum);
                var cells = row.Cells.Select(m => m.StringCellValue).ToArray();
                if (rowNum == sheet.FirstRowNum)
                {
                    titleSize = cells.Length;
                    dataTable.AddColumns(cells);
                }
                else
                {
                    dataTable.AddRow(cells.Take(titleSize).ToArray());
                }
            }
            return dataTable;
        }
    }
}
